/**
 * Copyright 2012 Woergi
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.gcmsms.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Datastore {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/gcmsms";
	// Database credentials
	static final String USER = "root";
	static final String PASS = "root";

	private static Datastore instance = new Datastore();

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;

	private Datastore() {
	}

	private void openDB() throws ClassNotFoundException, SQLException {
		// Register JDBC driver
		Class.forName("com.mysql.jdbc.Driver");

		// Open a connection
		conn = DriverManager.getConnection(DB_URL, USER, PASS);
		
		stmt = conn.createStatement();
	}

	private void executeSql(String sql) throws ClassNotFoundException,
			SQLException {
		if (sql == null)
			return;
		if (conn == null)
			openDB();

		// Execute SQL query
		rs = stmt.executeQuery(sql);
	}

	private void closeRes() {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		rs = null;
		stmt = null;
		conn = null;
	}

	public static Datastore getInstance() {
		return instance;
	}

	public boolean authenticateCredentials(String user, String password) {
		if (user == null || password == null || user.equals("nobody"))
			return false;

		boolean result = false;

		try {
			// Execute SQL query
			String sql = "SELECT name, password FROM users WHERE name='" + user
					+ "' AND password='" + password + "';";
			executeSql(sql);

			// Extract data from result set
			while (rs.next()) {
				// Retrieve by column name
				String name = rs.getString("name");
				String pass = rs.getString("password");
				if (name != null && pass != null)
					if (name.equals(user) && pass.equals(password))
						result = true;
			}
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			closeRes();
		} // end try

		return result;
	}
	
	public boolean registerNewDevice(String devId, String devNick) {
		boolean result = false;
		try {
			executeSql("SELECT count(*) AS 'DevNr' FROM devices WHERE deviceID='" + devId + "';");
			
			rs.next();
			if (rs.getInt("DevNr") == 0) {
				// insert new device
				stmt.executeUpdate("INSERT INTO devices set deviceID='" + devId + "', deviceNick='" + devNick + "';");
			} else {
				// update already registered device
				stmt.executeUpdate("UPDATE devices SET deviceNick='" + devNick + "' WHERE deviceID='" + devId + "';");
			}
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		} finally {
			closeRes();
		}
		return result;
	}
	
	public void updateRegistration(String oldId, String newId) {
		try {
			if (conn == null)
				openDB();
			stmt.executeUpdate("UPDATE devices SET deviceID='" + newId + "' WHERE deviceID='" + oldId + "';");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
	}
	
	public boolean unregisterDevice(String devId) {
		boolean result = false;
		try {
			executeSql("SELECT ID FROM devices WHERE deviceID='" + devId + "';");
			rs.next();
			int id = rs.getInt("ID");
			
			// Delete all contacts
			stmt.executeUpdate("DELETE FROM contacts WHERE deviceId='" + id + "';");
			
			// Delete user-device mapping
			stmt.executeUpdate("DELETE FROM userDevice WHERE deviceId='" + id + "';");
			
			// Delete device
			stmt.executeUpdate("DELETE FROM devices WHERE ID='" + id + "';");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
		return result;
	}
	
	public boolean updateDeviceContacts(String devId, String contacts) {
		boolean result = false;
		try {
			executeSql("SELECT ID from devices WHERE deviceID='" + devId +"';");
			rs.next();
			int id = rs.getInt("ID");
			
			stmt.executeUpdate("DELETE FROM contacts WHERE deviceId='" + id + "';");
			String[] names = contacts.split("\\|");
			for (int i = 0; i < names.length; ++i) {
				stmt.executeUpdate("INSERT INTO contacts SET deviceId='" + id + "', name='" + names[i] + "';");
			}
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
		return result;
	}

	public List<DeviceEntry> getDevices(String user) {
		List<DeviceEntry> resultList = new ArrayList<DeviceEntry>();
		
		try {
			executeSql("SELECT devices.ID, devices.deviceId, devices.deviceNick FROM devices,userDevice,users " +
					"WHERE devices.ID=userDevice.deviceID AND userDevice.userID=users.ID AND users.Name='" + user + "';");
			while (rs.next()) {
				resultList.add(new DeviceEntry(rs.getInt("ID"), rs.getString("deviceId"), rs.getString("deviceNick"), user));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
		return resultList;
	}
	
	public List<String> getContacts(int devId) {
		List<String> resultList = new ArrayList<String>();
		try {
			executeSql("SELECT contacts.name FROM contacts WHERE contacts.deviceId='" + devId + "';");
			while (rs.next()) {
				resultList.add(rs.getString("name"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
		return resultList;
	}

	public void removeDevForUser(String user, String toRemoveDev) {
		try {
			if (conn == null)
				openDB();
			stmt.executeUpdate("DELETE FROM userDevice WHERE deviceId='" + toRemoveDev
					+ "' AND userId=(SELECT ID FROM users WHERE name='" + user + "');");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeRes();
		}
	}
}
